package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.Activity;
import cn.edu.sdjzu.xg.kcsj.domain.Faculty;
import cn.edu.sdjzu.xg.kcsj.domain.SA;
import cn.edu.sdjzu.xg.kcsj.exception.BysjException;
import cn.edu.sdjzu.xg.kcsj.security.User;
import cn.edu.sdjzu.xg.kcsj.security.UserService;
import cn.edu.sdjzu.xg.kcsj.service.ActivityService;
import cn.edu.sdjzu.xg.kcsj.service.SAService;
import cn.edu.sdjzu.xg.kcsj.util.CommonDao;
import cn.edu.sdjzu.xg.kcsj.util.Condition;
import cn.edu.sdjzu.xg.kcsj.util.JdbcHelper;
import cn.edu.sdjzu.xg.kcsj.util.Pagination;


import java.sql.*;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public final class SADao {
    private static SADao sADao = new SADao();
    private SADao() {
    }

    public static SADao getInstance() {
        return sADao;
    }
    public boolean add(SA sa, Connection connection) throws SQLException {
        //准备预编译的语句
        String sql_insert = "INSERT INTO SA(score,student_id,activity_id,state) VALUES (?, ?, ?, ?)";
        PreparedStatement pstmt_update = connection.prepareStatement(sql_insert);
        pstmt_update.setInt(1, sa.getScore());
        pstmt_update.setInt(2, sa.getStudent().getId());
        pstmt_update.setInt(3, sa.getActivity().getId());
        pstmt_update.setInt(4, sa.getState());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(pstmt_update);
        return rowAffected > 0;
    }

    public SA find(int id, Connection connection) throws SQLException, BysjException {
        //创建Department类型的引用，暂不创建对象
        SA desiredSA = null;
        //获得数据库连接
        String sql_select_id = "SELECT * FROM SA WHERE id = ?";
        PreparedStatement pstmt_select_id =
                connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        while(resultSet_select_id.next()){
            int score = resultSet_select_id.getInt("score");
            int studentId = resultSet_select_id.getInt("student_id");
            int activityId = resultSet_select_id.getInt("activity_id");
            int available_id = resultSet_select_id.getInt("state");
            //获得当前Department隶属的学院
            User user = UserService.getInstance().find(studentId);
            Activity activity = ActivityService.getInstance().find(activityId);
            desiredSA = new SA(id, score, user, activity, available_id);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id);
        return desiredSA;
    }

    public Collection<SA> findAll() throws SQLException, BysjException {
        //创建集合类对象，用来保存所有记录代表的Department对象
        //TreeSet按compare(Object o)方法指定的顺序排序
        Collection<SA> desiredSAs = new TreeSet<SA>();
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_select_all = "SELECT * FROM sa";
        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select_all);
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while(resultSet_select_all.next()){
            int id = resultSet_select_all.getInt("id");
            int score = resultSet_select_all.getInt("score");
            int studentId = resultSet_select_all.getInt("student_id");
            int activityId = resultSet_select_all.getInt("activity_id");
            int state = resultSet_select_all.getInt("state");
            User user = UserService.getInstance().find(studentId);
            Activity activity = ActivityService.getInstance().find(activityId);
            SA desiredSA = new SA(id,score, user, activity, state);
            desiredSAs.add(desiredSA);
        }
        JdbcHelper.close(resultSet_select_all,pstmt_select_all,connection);
        return desiredSAs;
    }
    public Collection<SA> findAll(List conditionList, Pagination pagination, Connection connection) throws SQLException, BysjException {
        //创建集合类对象，用来保存所有记录代表的sa对象
        Collection<SA> sas = new TreeSet<SA>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection,"sa");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM sa ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
            //遍历resultSet
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                int score = resultSet.getInt("score");
                int studentId = resultSet.getInt("student_id");
                int activityId = resultSet.getInt("activity_id");
                int state = resultSet.getInt("state");
                User user = UserService.getInstance().find(studentId);
                Activity activity = ActivityService.getInstance().find(activityId);
                SA desiredSA = new SA(id,score, user, activity, state);
                sas.add(desiredSA);
        }
        JdbcHelper.close(resultSet,pstmt);
        return sas;
    }

    public boolean update(SA sa,Connection connection) throws SQLException {
        //创建sql语句
        String updateSchool_sql = "update sa set score=?,student_id=?,activity_id=?,state=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(5,sa.getId());
        pstmt.setInt(1,sa.getScore());
        pstmt.setInt(2,sa.getStudent().getId());
        pstmt.setInt(3,sa.getActivity().getId());
        pstmt.setInt(4,sa.getState());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }

    public boolean update(SA sa) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_update =
                "update sa set score=?,student_id=?,activity_id=?,state=? where id=?";
        PreparedStatement pstmt = connection.prepareStatement(sql_update);
        pstmt.setInt(1,sa.getScore());
        pstmt.setInt(2,sa.getStudent().getId());
        pstmt.setInt(3,sa.getActivity().getId());
        pstmt.setInt(4,sa.getState());
        pstmt.setInt(5,sa.getId());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,connection);
        return rowAffected > 0;
    }
    public boolean delete(int id,Connection connection) throws SQLException{
        //创建sql语句
        String delete_sql = "DELETE FROM sa WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(delete_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if (pstmt.executeLargeUpdate() == 1){
            return true;
        }else {
            return false;
        }
    }
    //寻找一个学生的所有活动记录
    public Collection<SA> getAllActivitiesOfStudent(User student) throws SQLException, BysjException {
        Collection<SA> sAs = new TreeSet<>();
        for (SA sA : sADao.findAll()) {
            if (sA.getStudent().getId() == student.getId()) {
                sAs.add(sA);
            }
        }
        return sAs;
    }

    public Collection<User> getAllStudent(Connection connection) throws SQLException, BysjException {
        StringBuilder sql_select = new StringBuilder("SELECT * FROM user ");
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        Collection<User> sas = new TreeSet<>();
        while (resultSet.next()) {
            String username = resultSet.getString("username");
            char c = username.charAt(username.length() - 1);
            if (c == '2') {
                Integer studentId = resultSet.getInt("id");
                User student = UserService.getInstance().find(studentId);
                sas.add(student);
            }
        }
        return sas;
    }
}